const db = require('../config/database');

class Goods {
  constructor(
    id = null,
    name,
    sku,
    quantity = 0,
    location = null,
    status = 'inStock',
    batchNumber = null,
    expiryDate = null,
    specification = null,
    unit = '件'
  ) {
    this.id = id;
    this.name = name;
    this.sku = sku;
    this.quantity = quantity;
    this.location = location;  // 货位编号，格式应为 WH01-A-01-01-01
    this.status = status;
    this.batchNumber = batchNumber;
    this.expiryDate = expiryDate;
    this.specification = specification;
    this.unit = unit;
  }

  // 保存货物
  save(callback) {
    const sql = `
      INSERT INTO goods (
        name, sku, quantity, location, status, batch_number, expiry_date, specification, unit
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    const params = [
      this.name, this.sku, this.quantity, this.location, this.status,
      this.batchNumber, this.expiryDate, this.specification, this.unit
    ];

    db.run(sql, params, function(err) {
      if (err) {
        return callback(err);
      }
      callback(null, { id: this.lastID });
    });
  }

  // 获取所有货物
  static findAll(callback) {
    const sql = 'SELECT * FROM goods ORDER BY id DESC';
    db.all(sql, [], (err, rows) => {
      if (err) {
        return callback(err);
      }
      callback(null, rows);
    });
  }

  // 根据查询条件获取货物
  static findByQuery(queryParams, callback) {
    let sql = 'SELECT * FROM goods WHERE 1=1';
    const params = [];
    
    // 物料编号/SKU查询
    if (queryParams.sku) {
      sql += ' AND sku LIKE ?';
      params.push(`%${queryParams.sku}%`);
    }
    
    // 物料名称查询
    if (queryParams.name) {
      sql += ' AND name LIKE ?';
      params.push(`%${queryParams.name}%`);
    }
    
    // 货位精确查询
    if (queryParams.location) {
      sql += ' AND location = ?';
      params.push(queryParams.location);
    }
    
    // 巷道查询
    if (queryParams.aisle) {
      sql += ' AND location LIKE ?';
      params.push(`%-${queryParams.aisle}-%`);
    }
    
    // 排查询
    if (queryParams.row) {
      sql += ' AND location LIKE ?';
      params.push(`%-${queryParams.row}-%`);
    }
    
    // 列查询
    if (queryParams.column) {
      sql += ' AND location LIKE ?';
      params.push(`%-${queryParams.column}-%`);
    }
    
    // 层查询
    if (queryParams.level) {
      sql += ' AND location LIKE ?';
      params.push(`%-${queryParams.level}%`);
    }
    
    // 库区查询
    if (queryParams.warehouseArea) {
      sql += ' AND location LIKE ?';
      params.push(`${queryParams.warehouseArea}-%`);
    }
    
    sql += ' ORDER BY id DESC';
    
    db.all(sql, params, (err, rows) => {
      if (err) {
        return callback(err);
      }
      callback(null, rows);
    });
  }

  // 根据ID获取货物
  static findById(id, callback) {
    const sql = 'SELECT * FROM goods WHERE id = ?';
    db.get(sql, [id], (err, row) => {
      if (err) {
        return callback(err);
      }
      callback(null, row);
    });
  }

  // 根据SKU获取货物
  static findBySku(sku, callback) {
    const sql = 'SELECT * FROM goods WHERE sku = ?';
    db.get(sql, [sku], (err, row) => {
      if (err) {
        return callback(err);
      }
      callback(null, row);
    });
  }

  // 根据货位获取货物
  static findByLocation(location, callback) {
    const sql = 'SELECT * FROM goods WHERE location = ?';
    db.get(sql, [location], (err, row) => {
      if (err) {
        return callback(err);
      }
      callback(null, row);
    });
  }

  // 更新货物
  static update(id, goodsData, callback) {
    const sql = `
      UPDATE goods 
      SET name = ?, sku = ?, quantity = ?, location = ?, status = ?, 
          batch_number = ?, expiry_date = ?, specification = ?, unit = ?
      WHERE id = ?
    `;
    
    const params = [
      goodsData.name, goodsData.sku, goodsData.quantity, goodsData.location, goodsData.status,
      goodsData.batchNumber, goodsData.expiryDate, goodsData.specification, goodsData.unit, id
    ];
    
    db.run(sql, params, function(err) {
      if (err) {
        return callback(err);
      }
      callback(null, { updated: this.changes > 0 });
    });
  }

  // 删除货物
  static delete(id, callback) {
    const sql = 'DELETE FROM goods WHERE id = ?';
    db.run(sql, [id], function(err) {
      if (err) {
        return callback(err);
      }
      callback(null, { deleted: this.changes > 0 });
    });
  }
}

module.exports = Goods;